﻿<%@ Page Title="" Language="C#" MasterPageFile="~/common/Admin.master" AutoEventWireup="true" CodeFile="Statistics.aspx.cs" Inherits="Statistics_Statistics" %>

<asp:Content ID="Content1" ContentPlaceHolderID="html_css" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="html_js" Runat="Server">
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    </style>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="leftMenu" Runat="Server">
    <ul>
        <li><a href="<%= Page.ResolveUrl("~/statistics/Statistics.aspx")%>">Age Group Statistics</a></li>
        <li><a href="<%= Page.ResolveUrl("~/statistics/TransactionStatistics.aspx")%>">Transation Statistics</a></li>
       
    </ul>



</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="content" Runat="Server">
    <p>
        &nbsp;</p>
    <p>
        <table class="style1">
            <tr>
                <td>
                   <h2> Customer Age Group Trend</h2></td>
                <td>
                    <h2>Customer List</h2></td>
            </tr>
            <tr>
                <td>
    <asp:SqlDataSource ID="SqlDataSourceCustomerAgeTrend" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT        COUNT(custNo) AS CUST_CNT, ageGroup
FROM            (SELECT        custNo, CASE WHEN age &lt;= 15 THEN '0-15' WHEN age &lt;= 28 AND age &gt;= 16 THEN '16-28' ELSE 'Above 28' END AS ageGroup
                          FROM            (SELECT        custID AS custNo, CAST(CONVERT(nvarchar(4), SYSDATETIME(), 112) AS int) - CAST(CONVERT(nvarchar(4), custDob, 112) AS int) AS age
                                                    FROM            CUSTOMER AS C) AS SUB) AS SUBAGE
GROUP BY ageGroup">
    </asp:SqlDataSource>
    <asp:Chart ID="CustomerAgeTrendChart" runat="server" 
        DataSourceID="SqlDataSourceCustomerAgeTrend">
        <Series>
            <asp:Series ChartType="Pie" Name="Series1" XValueMember="ageGroup" 
                YValueMembers="CUST_CNT">
            </asp:Series>
        </Series>
        <ChartAreas>
            <asp:ChartArea Name="ChartArea1">
            </asp:ChartArea>
        </ChartAreas>
    </asp:Chart>
                </td>
                <td valign="top">
                    <asp:GridView ID="GridView1" runat="server" BackColor="White" 
                        BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" 
                        ForeColor="Black" GridLines="Vertical" AllowPaging="True" 
                        AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="custID" 
                        DataSourceID="SqlDataSourceCustomerListAge">
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:BoundField DataField="custID" HeaderText="Cust ID" ReadOnly="True" 
                                SortExpression="custID" />
                            <asp:BoundField DataField="age" HeaderText="Age" ReadOnly="True" 
                                SortExpression="age" />
                            <asp:BoundField DataField="custNationality" HeaderText="Nationality" 
                                SortExpression="custNationality" />
                            <asp:BoundField DataField="custMarital" HeaderText="Marital Status" 
                                SortExpression="custMarital" />
                            <asp:BoundField DataField="custOccupation" HeaderText="Occupation" 
                                SortExpression="custOccupation" />
                        </Columns>
                        <FooterStyle BackColor="#CCCC99" />
                        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                        <RowStyle BackColor="#F7F7DE" />
                        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                        <SortedAscendingCellStyle BackColor="#FBFBF2" />
                        <SortedAscendingHeaderStyle BackColor="#848384" />
                        <SortedDescendingCellStyle BackColor="#EAEAD3" />
                        <SortedDescendingHeaderStyle BackColor="#575357" />
                    </asp:GridView>
                    <asp:SqlDataSource ID="SqlDataSourceCustomerListAge" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT custID, CAST(CONVERT (nvarchar(4), SYSDATETIME(), 112) AS int) - CAST(CONVERT (nvarchar(4), custDob, 112) AS int) AS age, custNationality, custMarital, custOccupation FROM CUSTOMER
WHERE custID&lt;&gt;'PowerShop'
ORDER BY age"></asp:SqlDataSource>
                </td>
            </tr>
        </table>
    <br />
</p>
<br /><br />
    <table class="style1">
        <tr>
            <td>
                <h2>Debit Card</h2></td>
            <td>
                <h2>Fixed Deposit</h2></td>
            <td>
                <h2>Insurance</h2></td>
        </tr>
        <tr>
            <td>
                <asp:Chart ID="ChartDebitAgeTrend" runat="server" 
                    DataSourceID="SqlDataSourceDebitCustomerAgeTrend">
                    <Series>
                        <asp:Series ChartType="Pie" Name="Series1" XValueMember="ageGroup" 
                            YValueMembers="CUST_CNT">
                        </asp:Series>
                    </Series>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1">
                        </asp:ChartArea>
                    </ChartAreas>
                </asp:Chart>
                <asp:SqlDataSource ID="SqlDataSourceDebitCustomerAgeTrend" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                    SelectCommand="SELECT COUNT(custNo) AS CUST_CNT, ageGroup FROM (SELECT custNo, CASE WHEN age &lt;= 15 THEN '0-15' WHEN age &lt;= 28 AND age &gt;= 16 THEN '16-28' ELSE 'Above 28' END AS ageGroup FROM (SELECT custID AS custNo, CAST(CONVERT (nvarchar(4), SYSDATETIME(), 112) AS int) - CAST(CONVERT (nvarchar(4), custDob, 112) AS int) AS age FROM CUSTOMER AS C WHERE EXISTS (SELECT 1 AS Expr1 FROM cardGenerator AS card WHERE (custID = C.custID))) AS SUB) AS SUBAGE GROUP BY ageGroup">
                </asp:SqlDataSource>
            </td>
            <td>
                <asp:Chart ID="ChartFixedAgeTrend" runat="server" 
                    DataSourceID="SqlDataSourceFixedDepositCustomerAgeTrend">
                    <Series>
                        <asp:Series ChartType="Pie" Name="Series1" XValueMember="ageGroup" 
                            YValueMembers="CUST_CNT">
                        </asp:Series>
                    </Series>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1">
                        </asp:ChartArea>
                    </ChartAreas>
                </asp:Chart>
                <asp:SqlDataSource ID="SqlDataSourceFixedDepositCustomerAgeTrend" 
                    runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT        COUNT(custNo) AS CUST_CNT, ageGroup
FROM            (SELECT        custNo, CASE WHEN age &lt;= 15 THEN '0-15' WHEN age &lt;= 28 AND age &gt;= 16 THEN '16-28' ELSE 'Above 28' END AS ageGroup
                          FROM            (SELECT        custID AS custNo, CAST(CONVERT(nvarchar(4), SYSDATETIME(), 112) AS int) - CAST(CONVERT(nvarchar(4), custDob, 112) AS int) AS age
                                                    FROM            CUSTOMER AS C
 WHERE EXISTS
           ( 
             SELECT 1
               FROM FIXED_DEPOSIT F
                             WHERE F.custID = C.custID
           )

) AS SUB) AS SUBAGE

GROUP BY ageGroup"></asp:SqlDataSource>
            </td>
            <td>
                <asp:Chart ID="ChartInsuranceAgeTrend" runat="server" 
                    DataSourceID="SqlDataSourceInsuranceCustomerAgeTrend">
                    <Series>
                        <asp:Series ChartType="Pie" Name="Series1" XValueMember="ageGroup" 
                            YValueMembers="CUST_CNT">
                        </asp:Series>
                    </Series>
                    <ChartAreas>
                        <asp:ChartArea Name="ChartArea1">
                        </asp:ChartArea>
                    </ChartAreas>
                </asp:Chart>
                <asp:SqlDataSource ID="SqlDataSourceInsuranceCustomerAgeTrend" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT        COUNT(custNo) AS CUST_CNT, ageGroup
FROM            (SELECT        custNo, CASE WHEN age &lt;= 15 THEN '0-15' WHEN age &lt;= 28 AND age &gt;= 16 THEN '16-28' ELSE 'Above 28' END AS ageGroup
                          FROM            (SELECT        custID AS custNo, CAST(CONVERT(nvarchar(4), SYSDATETIME(), 112) AS int) - CAST(CONVERT(nvarchar(4), custDob, 112) AS int) AS age
                                                    FROM            CUSTOMER AS C
 WHERE EXISTS
           ( 
             SELECT 1
               FROM INSURANCE I
                             WHERE I.custID = C.custID
           )

) AS SUB) AS SUBAGE

GROUP BY ageGroup"></asp:SqlDataSource>
            </td>
        </tr>
    </table>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
</asp:Content>

